//
//  ContactDao.m
//  SouthChina
//
//  Created by creator_lq on 14/12/3.
//  Copyright (c) 2014年 科创. All rights reserved.
//

#import "ContactDao.h"

#define CONTACT_FIELDS  @"PK_ID,ID,NAME,PROF_NAME,CLASS_NAME,NJ,HEAD_IMG,SEX,QP,REF_ID,TYPE,DEPT,OPERATE,IS_ACTIVATED,CREATE_USER,ORG_TYPE,HAS_CHILD,OTHER_INFO,UPDATE_TIME"
#define CONTACT_VARIATE  @"?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?"


@implementation ContactDao

+(BOOL)updateContact:(Contact*)contact{
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    [self checkTableCreatedInDb:_dataBase];
    NSString * delsql = [NSString stringWithFormat:@"DELETE from contacts where PK_ID='%@'",contact.mPK_ID];
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO contacts (%@) VALUES (%@)",CONTACT_FIELDS,CONTACT_VARIATE];
    
    [_dataBase executeUpdate:delsql];
    BOOL worked =[_dataBase executeUpdate:sql,contact.mPK_ID,contact.mID,contact.mNAME,contact.mPROF_NAME,contact.mCLASS_NAME,contact.mNJ,contact.mHEAD_IMG,contact.mSEX,contact.mQP,contact.mREF_ID,contact.mTYPE,contact.mDEPT,contact.mOPERATE,contact.mIS_ACTIVATED,contact.mCREATE_USER,contact.mORG_TYPE,contact.mHAS_CHILD, contact.otherInfo,contact.upate_time];
    
    if(!worked){
        NSLog(@"失败--updateContact");
    }else{
        NSLog(@"成功updateContact=%@",contact.mNAME);
        
    }
    return worked;
    
}


+(BOOL)updataDBwithSQLiteStatement:(NSArray*) listmap{
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    [_dataBase beginTransaction];
    
    BOOL isRollBack = NO;
    @try {
        for (int i = 0; i<[listmap  count]; i++) {
            
            NSMutableDictionary *map = [[NSMutableDictionary alloc] initWithDictionary:[listmap objectAtIndex:i]];
            NSString *pkid= [map objectForKey:PK_ID];
            NSString *iid= [map objectForKey:ID];
            NSString *name= [map objectForKey:NAME];
            NSString *prof= [map objectForKey:PROF_NAME];
            NSString *class= [map objectForKey:CLASS_NAME];
            NSString *nj= [map objectForKey:NJ];
            NSString *head= [map objectForKey:HEAD_IMG];
            NSString *sex= [map objectForKey:SEX];
            NSString *qp= [map objectForKey:QP];
            NSString *refid= [map objectForKey:REF_ID];
            NSString *type= [map objectForKey:TYPE];
            NSString *dept= [map objectForKey:DEPT];
            NSString *operate= [map objectForKey:OPERATE];
            NSString *isactive= [map objectForKey:IS_ACTIVATED];
            NSString *create_user= [map objectForKey:CREATE_USER];
            NSString *org_type= [map objectForKey:ORG_TYPE];
            NSString *has_child= [map objectForKey:HAS_CHILD];
            
            NSDate *updatime= [[map objectForKey:UPDATE_TIME] dateFromString];
            
            [map removeObjectForKey:PK_ID];
            [map removeObjectForKey:ID];
            [map removeObjectForKey:NAME];
            [map removeObjectForKey:PROF_NAME];
            [map removeObjectForKey:CLASS_NAME];
            [map removeObjectForKey:NJ];
            [map removeObjectForKey:HEAD_IMG];
            [map removeObjectForKey:SEX];
            [map removeObjectForKey:QP];
            [map removeObjectForKey:REF_ID];
            [map removeObjectForKey:TYPE];
            [map removeObjectForKey:DEPT];
            [map removeObjectForKey:OPERATE];
            [map removeObjectForKey:IS_ACTIVATED];
            [map removeObjectForKey:CREATE_USER];
            [map removeObjectForKey:ORG_TYPE];
            [map removeObjectForKey:HAS_CHILD];
            [map removeObjectForKey:UPDATE_TIME];
            
            NSString *otherinf = [map JSONString];
            
            NSString * delsql = [NSString stringWithFormat:@"DELETE from contacts where PK_ID='%@'",pkid];

            NSString *sql = [NSString stringWithFormat:@"INSERT INTO contacts (%@) VALUES (%@)",CONTACT_FIELDS,CONTACT_VARIATE];
            
            [_dataBase executeUpdate:delsql];
            BOOL a =[_dataBase executeUpdate:sql,pkid,iid,name,prof,class,nj,head,sex,qp,refid,type,dept,operate,isactive,create_user,org_type,has_child,otherinf,updatime];
                
            if(!a){
                NSLog(@"失败--");
            }else{
//                NSLog(@"成功=%@",name);
            }
            
        }
    }
    @catch (NSException *exception) {
        
        NSLog(@"插入失败--------: %@",exception);
        
        isRollBack = YES;
        [_dataBase rollback];
    }
    @finally {
        if (!isRollBack) {
            [_dataBase commit];
        }
    }
    
    [_dataBase close];
    
    return YES;
}

+(BOOL)updataDBwithSQLiteStatement:(NSArray*) listmap clearFisrt:(BOOL)clear{
    
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    [_dataBase beginTransaction];
    
    BOOL isRollBack = NO;
    @try {
        
        if (clear) {
            NSString * delsql = [NSString stringWithFormat:@"DELETE from contacts "];
            [_dataBase executeUpdate:delsql];
        }
        
        for (int i = 0; i<[listmap  count]; i++) {
            
            NSMutableDictionary *map = [[NSMutableDictionary alloc] initWithDictionary:[listmap objectAtIndex:i]];
            NSString *pkid= [map objectForKey:PK_ID];
            NSString *iid= [map objectForKey:ID];
            NSString *name= [map objectForKey:NAME];
            NSString *prof= [map objectForKey:PROF_NAME];
            NSString *class= [map objectForKey:CLASS_NAME];
            NSString *nj= [map objectForKey:NJ];
            NSString *head= [map objectForKey:HEAD_IMG];
            NSString *sex= [map objectForKey:SEX];
            NSString *qp= [map objectForKey:QP];
            NSString *refid= [map objectForKey:REF_ID];
            NSString *type= [map objectForKey:TYPE];
            NSString *dept= [map objectForKey:DEPT];
            NSString *operate= [map objectForKey:OPERATE];
            NSString *isactive= [map objectForKey:IS_ACTIVATED];
            NSString *create_user= [map objectForKey:CREATE_USER];
            NSString *org_type= [map objectForKey:ORG_TYPE];
            NSString *has_child= [map objectForKey:HAS_CHILD];
            
            //测试
            if([name isEqualToString:@"test5"])
                NSLog(@"pk_id:%@, name:%@,  未被删除掉,状态：%@",pkid,name,operate);
            
            NSDate *updatime= [[map objectForKey:UPDATE_TIME] dateFromString];
            
            [map removeObjectForKey:PK_ID];
            [map removeObjectForKey:ID];
            [map removeObjectForKey:NAME];
            [map removeObjectForKey:PROF_NAME];
            [map removeObjectForKey:CLASS_NAME];
            [map removeObjectForKey:NJ];
            [map removeObjectForKey:HEAD_IMG];
            [map removeObjectForKey:SEX];
            [map removeObjectForKey:QP];
            [map removeObjectForKey:REF_ID];
            [map removeObjectForKey:TYPE];
            [map removeObjectForKey:DEPT];
            [map removeObjectForKey:OPERATE];
            [map removeObjectForKey:IS_ACTIVATED];
            [map removeObjectForKey:CREATE_USER];
            [map removeObjectForKey:ORG_TYPE];
            [map removeObjectForKey:HAS_CHILD];
            [map removeObjectForKey:UPDATE_TIME];
            
            NSString *otherinf = [map JSONString];
            
            NSString * delsql = [NSString stringWithFormat:@"DELETE from contacts where PK_ID='%@'",pkid];

            NSString *sql = [NSString stringWithFormat:@"INSERT INTO contacts (%@) VALUES (%@)",CONTACT_FIELDS,CONTACT_VARIATE];
           
            [_dataBase executeUpdate:delsql];

            BOOL a =[_dataBase executeUpdate:sql,pkid,iid,name,prof,class,nj,head,sex,qp,refid,type,dept,operate,isactive,create_user,org_type,has_child,otherinf,updatime];
            
            if(!a){
                NSLog(@"失败");
            }else{
//                NSLog(@"成功=%@",name);
                
            }
            
        }
    }
    @catch (NSException *exception) {
        
        NSLog(@"插入失败--------: %@",exception);
        
        isRollBack = YES;
        [_dataBase rollback];
    }
    @finally {
        if (!isRollBack) {
            [_dataBase commit];
        }
    }
    
    [_dataBase close];
    
    return YES;

}


+(int)queryChildCount:(NSString*)orgid{
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    [self checkTableCreatedInDb:_dataBase];
    
    NSString * sql = @"SELECT count(*) as cc from contacts where ref_id=? and operate ='U'";
    FMResultSet *rs=[_dataBase executeQuery:sql,orgid];
    int count = 0;
    while ([rs next]) {
        count =[rs intForColumn:@"cc"];
    };
    [rs close];
    [_dataBase close];
    return count;
    
 }





/*!
 *  查询联系人分组情况，分校园通讯录和个人通讯录，
 *  如果是type=1，还需要在校园通讯录中增加最近联系人
 *
 *  @param type <#type description#>
 *
 *  @return <#return value description#>
 */
+(NSMutableArray*)queryOrgWithCount:(NSString*) type
{
    
    NSString *sql = [NSString stringWithFormat:@"select ref_id id, (case ref_id \
    when 'Classmate' then '我的同学'\
    when  'Teacher' then '我的老师'\
    when  'Instructor' then '我的辅导员'\
    when  'ClassStudent' then '我授课的学生'\
    when  'InstruStudent' then '我辅导的学生'\
    when  'Friend' then '我的好友'\
    when  'Roommate' then '我的舍友'\
    else ref_id end ) NAME,count(1) CNT  \
    from contacts a where type <> 'O' and operate ='U'\
    and ref_id in('Classmate','Teacher','Instructor','ClassStudent','InstruStudent','Friend','Roommate')\
    group by 1 "];
    
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    NSMutableArray * allList = [[NSMutableArray alloc] init];
    
    
    FMResultSet *rs=[_dataBase executeQuery:sql];
    while ([rs next]) {
        Contact *contact = [[Contact alloc] init];
        contact.mID =[rs stringForColumn:ID];
        contact.mNAME =[rs stringForColumn:NAME];
        contact.otherInfo =[rs stringForColumn:@"CNT"];
        [allList addObject:contact];
        
    };
    [rs close];
    [_dataBase close];
    
    
    NSMutableArray * orgArray = [[NSMutableArray alloc] init];
    
    //NSPredicate *predicateOrg = [NSPredicate predicateWithFormat:@"mID != %@ ",@"Friend"];
    //NSArray * orgList = [allList filteredArrayUsingPredicate:predicateOrg];
    
    NSMutableArray * commContact = [[NSMutableArray alloc] init];
    
    if([type isEqualToString:@"1"])
    {
        //添加最近联系人
        Contact *contact = [[Contact alloc] init];
        contact.mID =RECENTLYCONTACT;
        contact.mNAME =@"最近联系人";
        [commContact addObject:contact];
       // [commContact addObjectsFromArray:orgList];
        [commContact addObjectsFromArray:allList];
    }
    else
    {
      //  [commContact addObjectsFromArray:orgList];
         [commContact addObjectsFromArray:allList];
    }
    
    NSMutableDictionary * orgData = [NSMutableDictionary dictionaryWithObjectsAndKeys:
                                     @"校园通讯录", @"name", commContact, @"list", nil];
    
    
    //if(orgList.count !=0)
    if(allList.count !=0)
        [orgArray addObject:orgData];
    
    
    //NSPredicate *predicateFriend = [NSPredicate predicateWithFormat:@"mID == %@ ",@"Friend"];
    //NSArray * FriendList = [allList filteredArrayUsingPredicate:predicateFriend];
    //NSMutableDictionary * FriendData = [NSMutableDictionary dictionaryWithObjectsAndKeys:
     //                                  @"个人通讯录", @"name", FriendList, @"list", nil];
    
    //if(FriendList.count !=0)
   // [orgArray addObject:FriendData];
    
    return orgArray;
    
}


/*!
 *  查询最近联系人
 *
 *  @return <#return value description#>
 */
+(NSArray*)queryRecentlyCcontact:(NSString*)key

{
    
    NSString *sql =[NSString stringWithFormat:@"select distinct a.id,a.name ,a.prof_name,a.class_name,a.nj,b.sessionpic head_img,\
    a.sex,a.qp,a.type,a.dept,a.is_activated from contacts a ,messagesession b\
    where b.oppositeId= a.id and b.sessiontype='p2p'\
    and a.operate ='U' and a.name like '%%%@%%' ",key];
    
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    NSMutableArray * orgList = [[NSMutableArray alloc] init];
    
    FMResultSet *rs=[_dataBase executeQuery:sql];
    while ([rs next]) {
        Contact *contact = [[Contact alloc] init];
        contact.mID =[rs stringForColumn:ID];
        contact.mNAME =[rs stringForColumn:NAME];
        contact.mPK_ID =[rs stringForColumn:PK_ID];
        contact.otherInfo =[rs stringForColumnIndex:3];
        
        
        [orgList addObject:contact];
        
    };
    [rs close];
    [_dataBase close];
    
    return orgList;
    
}

+(NSArray*)queryAllOrgWithCount {
    
    NSString *sql =@"select t1.id,t1.name,t1.pk_id,t2.count from contacts t1 , (select ref_id,count(*) count from contacts where type <> 'O' and operate='U'  group by ref_id) t2 where t1.id = t2.ref_id and t1.type = 'O' and t1.operate='U' order by  (case t1.ref_id when 'virtual' then 'z98' when 'usergrp' then 'z99' when 'msr' then  'zz9' when 'friend' then 'a01' else t1.ref_id end) ";
    
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    NSMutableArray * orgList = [[NSMutableArray alloc] init];
    
    FMResultSet *rs=[_dataBase executeQuery:sql];
    while ([rs next]) {
        Contact *contact = [[Contact alloc] init];
        contact.mID =[rs stringForColumn:ID];
        contact.mNAME =[rs stringForColumn:NAME];
        contact.mPK_ID =[rs stringForColumn:PK_ID];
        contact.otherInfo =[rs stringForColumnIndex:3];
        
        
        [orgList addObject:contact];
        
    };
    [rs close];
    [_dataBase close];

    return orgList;
    
}

+(NSArray*)getLeftNodeContact:(NSString*)orgid{

    NSString *sql =@"SELECT * from contacts where TYPE in('T','S') and   REF_ID =? and OPERATE='U'";
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    NSMutableArray * contactList = [[NSMutableArray alloc] init];
    
    FMResultSet *rs=[_dataBase executeQuery:sql,orgid];
    while ([rs next]) {
        Contact *contact = [[Contact alloc] init];
        contact.mID =[rs stringForColumn:ID];
        contact.mNAME =[rs stringForColumn:NAME];
        contact.mPK_ID =[rs stringForColumn:PK_ID];
        contact.mPROF_NAME =[rs stringForColumn:PROF_NAME];
        contact.mCLASS_NAME =[rs stringForColumn:CLASS_NAME];
        contact.mNJ =[rs stringForColumn:NJ];
        contact.mHEAD_IMG =[rs stringForColumn:HEAD_IMG];
        contact.mSEX =[rs stringForColumn:SEX];
        contact.mQP =[rs stringForColumn:QP];
        contact.mREF_ID =[rs stringForColumn:REF_ID];
        contact.mTYPE =[rs stringForColumn:TYPE];
        contact.mDEPT =[rs stringForColumn:DEPT];
        contact.mOPERATE =[rs stringForColumn:OPERATE];
        contact.mIS_ACTIVATED =[rs stringForColumn:IS_ACTIVATED];
        contact.upate_time=[rs dateForColumn:UPDATE_TIME];
        contact.otherInfo =[rs stringForColumn:OTHER_INFO];
        contact.mCREATE_USER= [rs stringForColumn:CREATE_USER];
        contact.mORG_TYPE= [rs stringForColumn:ORG_TYPE];
        contact.mHAS_CHILD= [rs stringForColumn:HAS_CHILD];
        [contactList addObject:contact];
        
    };
    [rs close];
    [_dataBase close];
    return contactList;

    
}

+(NSMutableArray*)queryOrgWithrefidandkey:(NSString*)key ConditionSQL:(NSString*)wheresql
{
    NSString *sql = [NSString stringWithFormat:@"select distinct(%@) keyname from contacts %@",key,wheresql];
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    [self checkTableCreatedInDb:_dataBase];
    
    NSMutableArray * returnList = [[NSMutableArray alloc] init];
    
    FMResultSet *rs=[_dataBase executeQuery:sql];
    while ([rs next]) {
        [returnList addObject:[rs stringForColumn:@"keyname"]];
    };
    [rs close];
    [_dataBase close];
    return returnList;
}



+(NSArray*)searcherNodeContact:(NSString*)orgid key:(NSString*)key{
    
   NSString * sql = [NSString stringWithFormat:@"SELECT * from contacts where REF_ID='%@' and OPERATE='U' and NAME like '%%%@%%'",orgid,key];
    NSLog(@"执行sql:%@",sql);
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    NSMutableArray * contactList = [[NSMutableArray alloc] init];
    
    FMResultSet *rs=[_dataBase executeQuery:sql,orgid];
    while ([rs next]) {
        Contact *contact = [[Contact alloc] init];
        contact.mID =[rs stringForColumn:ID];
        contact.mNAME =[rs stringForColumn:NAME];
        contact.mPK_ID =[rs stringForColumn:PK_ID];
        contact.mPROF_NAME =[rs stringForColumn:PROF_NAME];
        contact.mCLASS_NAME =[rs stringForColumn:CLASS_NAME];
         contact.mNJ =[rs stringForColumn:NJ];
        contact.mHEAD_IMG =[rs stringForColumn:HEAD_IMG];
        contact.mSEX =[rs stringForColumn:SEX];
        contact.mQP =[rs stringForColumn:QP];
        contact.mREF_ID =[rs stringForColumn:REF_ID];
        contact.mTYPE =[rs stringForColumn:TYPE];
        contact.mDEPT =[rs stringForColumn:DEPT];
        contact.mOPERATE =[rs stringForColumn:OPERATE];
        contact.mIS_ACTIVATED =[rs stringForColumn:IS_ACTIVATED];
        contact.upate_time=[rs dateForColumn:UPDATE_TIME];
        contact.otherInfo =[rs stringForColumn:OTHER_INFO];
        contact.mCREATE_USER= [rs stringForColumn:CREATE_USER];
        contact.mORG_TYPE= [rs stringForColumn:ORG_TYPE];
        contact.mHAS_CHILD= [rs stringForColumn:HAS_CHILD];

        
        [contactList addObject:contact];
        
    };
    [rs close];
    [_dataBase close];

    return contactList;

    
}


/**
 *	@brief	<#Description#>
 *
 *	@param 	ConditionSQL{ 	<#ConditionSQL{ description#>
 *
 *	@return	<#return value description#>
 */

+(Contact*)getContactByWhereSQL:(NSString*)ConditionSQL{
    NSString * sql = [NSString stringWithFormat:@"SELECT * from contacts  %@",ConditionSQL];
    NSLog(@"执行sql:%@",sql);
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    FMResultSet *rs=[_dataBase executeQuery:sql,ConditionSQL];
    Contact *contact = nil;
    while ([rs next]) {
        contact = [[Contact alloc] init];
        contact.mID =[rs stringForColumn:ID];
        contact.mNAME =[rs stringForColumn:NAME];
        contact.mPK_ID =[rs stringForColumn:PK_ID];
        contact.mPROF_NAME =[rs stringForColumn:PROF_NAME];
        contact.mCLASS_NAME =[rs stringForColumn:CLASS_NAME];
        contact.mNJ =[rs stringForColumn:NJ];
        contact.mHEAD_IMG =[rs stringForColumn:HEAD_IMG];
        contact.mSEX =[rs stringForColumn:SEX];
        contact.mQP =[rs stringForColumn:QP];
        contact.mREF_ID =[rs stringForColumn:REF_ID];
        contact.mTYPE =[rs stringForColumn:TYPE];
        contact.mDEPT =[rs stringForColumn:DEPT];
        contact.mOPERATE =[rs stringForColumn:OPERATE];
        contact.mIS_ACTIVATED =[rs stringForColumn:IS_ACTIVATED];
        contact.upate_time=[rs dateForColumn:UPDATE_TIME];
        contact.otherInfo =[rs stringForColumn:OTHER_INFO];
        contact.mCREATE_USER= [rs stringForColumn:CREATE_USER];
        contact.mORG_TYPE= [rs stringForColumn:ORG_TYPE];
        contact.mHAS_CHILD= [rs stringForColumn:HAS_CHILD];
        break;
    };
    [rs close];
    [_dataBase close];
    
    return contact;

    
    
}


+(NSArray*)getContactByWhere:(NSString*)ConditionSQL{
    
   
    NSString * sql = [NSString stringWithFormat:@"SELECT * from contacts %@",ConditionSQL];
     NSLog(@"执行1111sql:%@",sql);
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    NSMutableArray * contactList = [[NSMutableArray alloc] init];
    
    FMResultSet *rs=[_dataBase executeQuery:sql];
    while ([rs next]) {
        Contact *contact = [[Contact alloc] init];
        contact.mID =[rs stringForColumn:ID];
        contact.mNAME =[rs stringForColumn:NAME];
        contact.mPK_ID =[rs stringForColumn:PK_ID];
        contact.mPROF_NAME =[rs stringForColumn:PROF_NAME];
        contact.mCLASS_NAME =[rs stringForColumn:CLASS_NAME];
        contact.mNJ =[rs stringForColumn:NJ];
        contact.mHEAD_IMG =[rs stringForColumn:HEAD_IMG];
        contact.mSEX =[rs stringForColumn:SEX];
        contact.mQP =[rs stringForColumn:QP];
        contact.mREF_ID =[rs stringForColumn:REF_ID];
        contact.mTYPE =[rs stringForColumn:TYPE];
        contact.mDEPT =[rs stringForColumn:DEPT];
        contact.mOPERATE =[rs stringForColumn:OPERATE];
        contact.mIS_ACTIVATED =[rs stringForColumn:IS_ACTIVATED];
        contact.upate_time=[rs dateForColumn:UPDATE_TIME];
        contact.otherInfo =[rs stringForColumn:OTHER_INFO];
        contact.mCREATE_USER= [rs stringForColumn:CREATE_USER];
        contact.mORG_TYPE= [rs stringForColumn:ORG_TYPE];
        contact.mHAS_CHILD= [rs stringForColumn:HAS_CHILD];
        
        
        [contactList addObject:contact];
        
    };
    [rs close];
    [_dataBase close];
    
    return contactList;
    
}

////20151010剔重
+(NSArray*)getDistinctContactByWhere:(NSString*)ConditionSQL
{
    NSString * sql = ConditionSQL;
    NSLog(@"执行剔重sql:%@",sql);
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    NSMutableArray * contactList = [[NSMutableArray alloc] init];
    
    FMResultSet *rs=[_dataBase executeQuery:sql];
    while ([rs next]) {
        Contact *contact = [[Contact alloc] init];
        contact.mID =[rs stringForColumn:ID];
        contact.mNAME =[rs stringForColumn:NAME];
        contact.mPK_ID =[rs stringForColumn:PK_ID];
        contact.mPROF_NAME =[rs stringForColumn:PROF_NAME];
        contact.mCLASS_NAME =[rs stringForColumn:CLASS_NAME];
        contact.mNJ =[rs stringForColumn:NJ];
        contact.mHEAD_IMG =[rs stringForColumn:HEAD_IMG];
        contact.mSEX =[rs stringForColumn:SEX];
        contact.mQP =[rs stringForColumn:QP];
        contact.mREF_ID =[rs stringForColumn:REF_ID];
        contact.mTYPE =[rs stringForColumn:TYPE];
        contact.mDEPT =[rs stringForColumn:DEPT];
        contact.mOPERATE =[rs stringForColumn:OPERATE];
        contact.mIS_ACTIVATED =[rs stringForColumn:IS_ACTIVATED];
        contact.upate_time=[rs dateForColumn:UPDATE_TIME];
        contact.otherInfo =[rs stringForColumn:OTHER_INFO];
        contact.mCREATE_USER= [rs stringForColumn:CREATE_USER];
        contact.mORG_TYPE= [rs stringForColumn:ORG_TYPE];
        contact.mHAS_CHILD= [rs stringForColumn:HAS_CHILD];
        
        
        [contactList addObject:contact];
        
    };
    [rs close];
    [_dataBase close];
    
    return contactList;
}

+(Contact*)getContactByPKId:(NSString*)contacdId{
    
    NSString * sql = @"SELECT * from contacts where PK_ID=? and operate ='U'";
    
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    
    FMResultSet *rs=[_dataBase executeQuery:sql,contacdId];
    Contact *contact = nil;
    while ([rs next]) {
        contact = [[Contact alloc] init];
        contact.mID =[rs stringForColumn:ID];
        contact.mNAME =[rs stringForColumn:NAME];
        contact.mPK_ID =[rs stringForColumn:PK_ID];
        contact.mPROF_NAME =[rs stringForColumn:PROF_NAME];
        contact.mCLASS_NAME =[rs stringForColumn:CLASS_NAME];
        contact.mNJ =[rs stringForColumn:NJ];
        contact.mHEAD_IMG =[rs stringForColumn:HEAD_IMG];
        contact.mSEX =[rs stringForColumn:SEX];
        contact.mQP =[rs stringForColumn:QP];
        contact.mREF_ID =[rs stringForColumn:REF_ID];
        contact.mTYPE =[rs stringForColumn:TYPE];
        contact.mDEPT =[rs stringForColumn:DEPT];
        contact.mOPERATE =[rs stringForColumn:OPERATE];
        contact.mIS_ACTIVATED =[rs stringForColumn:IS_ACTIVATED];
        contact.upate_time=[rs dateForColumn:UPDATE_TIME];
        contact.otherInfo =[rs stringForColumn:OTHER_INFO];
        contact.mCREATE_USER= [rs stringForColumn:CREATE_USER];
        contact.mORG_TYPE= [rs stringForColumn:ORG_TYPE];
        contact.mHAS_CHILD= [rs stringForColumn:HAS_CHILD];
        break;
    };
    [rs close];
    [_dataBase close];

    return contact;
    
}

+(Contact*)getContactById:(NSString*)contacdId{
    NSString * sql = @"SELECT * from contacts where ID=? and operate ='U'";
    
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    FMResultSet *rs=[_dataBase executeQuery:sql,contacdId];
    Contact *contact = nil;
    while ([rs next]) {
        contact = [[Contact alloc] init];
        contact.mID =[rs stringForColumn:ID];
        contact.mNAME =[rs stringForColumn:NAME];
        contact.mPK_ID =[rs stringForColumn:PK_ID];
        contact.mPROF_NAME =[rs stringForColumn:PROF_NAME];
        contact.mCLASS_NAME =[rs stringForColumn:CLASS_NAME];
        contact.mNJ =[rs stringForColumn:NJ];
        contact.mHEAD_IMG =[rs stringForColumn:HEAD_IMG];
        contact.mSEX =[rs stringForColumn:SEX];
        contact.mQP =[rs stringForColumn:QP];
        contact.mREF_ID =[rs stringForColumn:REF_ID];
        contact.mTYPE =[rs stringForColumn:TYPE];
        contact.mDEPT =[rs stringForColumn:DEPT];
        contact.mOPERATE =[rs stringForColumn:OPERATE];
        contact.mIS_ACTIVATED =[rs stringForColumn:IS_ACTIVATED];
        contact.upate_time=[rs dateForColumn:UPDATE_TIME];
        contact.otherInfo =[rs stringForColumn:OTHER_INFO];
        contact.mCREATE_USER= [rs stringForColumn:CREATE_USER];
        contact.mORG_TYPE= [rs stringForColumn:ORG_TYPE];
        contact.mHAS_CHILD= [rs stringForColumn:HAS_CHILD];
        break;
    };
    [rs close];
    [_dataBase close];
    
    return contact;
    
}

+(BOOL)isMyFriend:(NSString*) pkid{
    
    NSString * sql = @"SELECT * from contacts where PK_ID=? and OPERATE ='U'";
    
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    FMResultSet *rs=[_dataBase executeQuery:sql,pkid];
    
    while ([rs next]) {
        return YES;
    };
    [rs close];
    [_dataBase close];
    
    return NO;

    
}

+(BOOL)insertStrangerOrg{
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];

    NSString *sql = [NSString stringWithFormat:@"INSERT INTO contacts (%@) VALUES (%@)",CONTACT_FIELDS,CONTACT_VARIATE];
    
    BOOL a =[_dataBase executeUpdate:sql,[NSString stringWithFormat:@"stranger%@",DE.userId],[NSString stringWithFormat:@"stranger%@",DE.userId],@"陌生人",@"",@"",@"",@"",@"",@"moshengren",@"msr",@"O",@"",@"U",@"",@"",@"",@"",nil,[NSDate date]];
    
    if(!a){
        NSLog(@"失败");
    }else{
        NSLog(@"成功");
        
    }
    [_dataBase close];

    return a;
}

+(BOOL)insertStrangerContactFromNet:(NSDictionary*)contcts{
    
    FMDatabase *_dataBase = [DateBaseHelper openDateBase];
    
    [self checkTableCreatedInDb:_dataBase];
    
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO contacts (%@) VALUES (%@)",CONTACT_FIELDS,CONTACT_VARIATE];
    
    
    BOOL a =[_dataBase executeUpdate:sql,[NSString stringWithFormat:@"stranger_%@",[contcts objectForKey:@"userId"]],[contcts objectForKey:@"userId"],[contcts objectForKey:@"userName"],@"profName",[contcts objectForKey:@"className"],[contcts objectForKey:@"nj"],[contcts objectForKey:@"headImg"],[contcts objectForKey:@"sex"],[contcts objectForKey:@"qp"],[NSString stringWithFormat:@"stranger%@",DE.userId],[contcts objectForKey:@"type"],[contcts objectForKey:@"dept"],@"U",@"1",[contcts objectForKey:@"create_user"],[contcts objectForKey:@"org_type"],[contcts objectForKey:@"has_child"],nil,[NSDate date]];
    
    if(!a){
        NSLog(@"失败");
    }else{
        NSLog(@"成功");
        
    }
    [_dataBase close];
    
    return a;

    
}

+(BOOL)checkTableCreatedInDb:(FMDatabase *)db
{
    NSString *createStr=@"CREATE  TABLE  IF NOT EXISTS 'contacts' ('PK_ID' text PRIMARY KEY  NOT NULL  UNIQUE ,'ID' text,'NAME' text, 'PROF_NAME' text,'CLASS_NAME' text,'NJ' text,'HEAD_IMG' text,'SEX' text,'QP' text,'REF_ID' text,'TYPE' text,'DEPT' text,'OPERATE' text,'IS_ACTIVATED' text,'CREATE_USER' text ,'ORG_TYPE' text,'HAS_CHILD' text,'OTHER_INFO' text,'UPDATE_TIME' date)";
    
    BOOL worked = [db executeUpdate:createStr];
    FMDBQuickCheck(worked);
    return worked;
}


@end
